package freakin.wify;

import java.util.HashSet;
import java.util.List;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbHelper extends SQLiteOpenHelper {

	private static final String TAG = "DbHelper";

	static final String DATABASE = "networks.db";
	static final int VERSION = 5;

	public static final String tableName = "networks";

	public static final String DB_N_ID = "_id";
	public static final String DB_N_BSSID = "bssid";
	public static final String DB_N_SSID = "ssid";
	public static final String DB_N_FREQUENCY = "frequency";
	public static final String DB_N_LEVEL = "level"; // power lvl, in dBm
	public static final String DB_N_SECURITY = "security";
	public static final String DB_N_LOC_LAT = "latitude";
	public static final String DB_N_LOC_LON = "longitude";
	public static final String DB_N_LOC_SET = "location_set"; // if location of
																// the network
																// is know,
																// it'll be true
	public static final String DB_N_OPEN = "open"; // is this network an open
													// one?
	public static final String DB_N_SEND = "send"; // has it been send to the
													// website?

	public DbHelper(Context context) {
		super(context, DATABASE, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(TAG, "Creating database: " + DATABASE);

		db.execSQL("create table " + tableName + " (" + DB_N_ID
				+ " integer primary key autoincrement, " + DB_N_BSSID
				+ " text, " + DB_N_SSID + " text, " + DB_N_FREQUENCY
				+ " integer, " + DB_N_LEVEL + " integer, " + DB_N_SECURITY
				+ " text, " + DB_N_LOC_LAT + " real, " + DB_N_LOC_LON
				+ " real, " + DB_N_LOC_SET + " integer," + DB_N_OPEN
				+ " integer default 0, " + DB_N_SEND + " integer default 0)");
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("drop table if exists " + tableName);
		this.onCreate(db);
	}

	/**
	 * Insert a network to the database.
	 * 
	 * @param n
	 *            network to be saved.
	 * @return id of the row in the db.
	 */
	public long insertNetwork(Network n) {

		SQLiteDatabase db = getWritableDatabase();
		long id = -1;

		try {
			ContentValues values = new ContentValues();
			values.put(DbHelper.DB_N_BSSID, n.bssid);
			values.put(DbHelper.DB_N_SSID, n.ssid);
			values.put(DbHelper.DB_N_FREQUENCY, n.frequency);
			values.put(DbHelper.DB_N_LEVEL, n.level);
			values.put(DbHelper.DB_N_SECURITY, n.security);
			if (n.locationSet) {
				values.put(DbHelper.DB_N_LOC_LAT, n.latitude);
				values.put(DbHelper.DB_N_LOC_LON, n.longitude);
				values.put(DbHelper.DB_N_LOC_SET, 1);
			}
			values.put(DB_N_SEND, n.send);
			values.put(DB_N_OPEN, n.open);

			id = db.insertWithOnConflict(DbHelper.tableName, null, values,
					SQLiteDatabase.CONFLICT_IGNORE);
		} catch (SQLException s) {
			Log.e(TAG, "insertOrIgnore exception");
			s.printStackTrace();
		} finally {
			db.close();
		}

		return id;
	}

	/**
	 * Insert many rows at once.
	 * 
	 * @param nets
	 */
	public void insertNetworks(List<Network> nets) {
		SQLiteDatabase db = getWritableDatabase();

		try {
			ContentValues values = new ContentValues();
			for (Network n : nets) {
				values.clear();
				values.put(DbHelper.DB_N_BSSID, n.bssid);
				values.put(DbHelper.DB_N_SSID, n.ssid);
				values.put(DbHelper.DB_N_FREQUENCY, n.frequency);
				values.put(DbHelper.DB_N_LEVEL, n.level);
				values.put(DbHelper.DB_N_SECURITY, n.security);
				if (n.locationSet) {
					values.put(DbHelper.DB_N_LOC_LAT, n.latitude);
					values.put(DbHelper.DB_N_LOC_LON, n.longitude);
					values.put(DbHelper.DB_N_LOC_SET, 1);
				}
				values.put(DB_N_SEND, n.send);
				values.put(DB_N_OPEN, n.open);

				db.insertWithOnConflict(DbHelper.tableName, null, values,
						SQLiteDatabase.CONFLICT_IGNORE);
			}

		} catch (SQLException s) {
			Log.e(TAG, "insertOrIgnore exception");
			s.printStackTrace();
		} finally {
			db.close();
		}
	}

	/**
	 * Pobiera Siec z wszelkimi danymi.
	 * 
	 * @param bssid
	 * @param ssid
	 * @return sieci, lub null, jesli nie ma takiej.
	 */
	public Network getNetwork(String bssid, String ssid) {
		Network n = null;

		long id = searchForNetwork(bssid, ssid);
		if (id < 0)
			return null;

		SQLiteDatabase db = null;

		Cursor c = null;

		try {
			db = getReadableDatabase();

			c = db.rawQuery("SELECT " + "*" + " FROM " + DbHelper.tableName
					+ " WHERE " + DB_N_ID + " = " + id, null);

			c.moveToFirst();

			int db_id = c.getColumnIndexOrThrow(DB_N_ID);
			int db_bssid = c.getColumnIndexOrThrow(DB_N_BSSID);
			int db_lat = c.getColumnIndexOrThrow(DB_N_LOC_LAT);
			int db_lon = c.getColumnIndexOrThrow(DB_N_LOC_LON);
			int db_loc = c.getColumnIndexOrThrow(DB_N_LOC_SET);
			// int db_send = c.getColumnIndexOrThrow(DB_N_SEND);
			int db_freq = c.getColumnIndexOrThrow(DB_N_FREQUENCY);
			int db_lvl = c.getColumnIndexOrThrow(DB_N_LEVEL);
			int db_open = c.getColumnIndexOrThrow(DB_N_OPEN);
			int db_security = c.getColumnIndexOrThrow(DB_N_SECURITY);
			int db_ssid = c.getColumnIndexOrThrow(DB_N_SSID);

			n = new Network();
			n.id = c.getLong(db_id);
			n.bssid = c.getString(db_bssid);
			n.latitude = c.getDouble(db_lat);
			n.longitude = c.getDouble(db_lon);
			n.locationSet = ((c.getInt(db_loc) == 0) ? false : true);
			n.send = false;
			n.frequency = c.getInt(db_freq);
			n.level = c.getInt(db_lvl);
			n.open = ((c.getInt(db_open) == 0) ? false : true);
			n.security = c.getString(db_security);
			n.ssid = c.getString(db_ssid);

		} catch (SQLException se) {
			Log.d(TAG, "searchForNetwork sql exception");
			se.printStackTrace();
		} finally {
			if (c != null)
				c.close();
			if (db != null)
				db.close();
		}

		return n;
	}

	/**
	 * 
	 * @param bssid
	 * @param ssid
	 * @return id of the row in the db.
	 */
	public long searchForNetwork(String bssid, String ssid) {
		long id = -1;

		SQLiteDatabase db = null;

		Cursor c = null;

		try {
			db = getReadableDatabase();

			if (bssid == null) {
				if (ssid == null) {
					return -1;
				} else {
					// lookup just ssid
					c = db.rawQuery("SELECT " + DB_N_ID + " FROM "
							+ DbHelper.tableName + " WHERE " + DB_N_SSID
							+ " = \"" + ssid + "\"", null);

					if (c.getColumnCount() > 0 && c.getCount() > 0) {
						c.moveToFirst();
						id = c.getLong(c.getColumnIndex(DB_N_ID));
					}
				}
			} else if (ssid == null) {
				// lookup just bssid
				c = db.rawQuery("SELECT " + DB_N_ID + " FROM "
						+ DbHelper.tableName + " WHERE " + DB_N_BSSID + " = \""
						+ bssid + "\"", null);
				
				if (c.getColumnCount() > 0 && c.getCount() > 0) {
					c.moveToFirst();
					id = c.getLong(c.getColumnIndex(DB_N_ID));
				}

			} else {
				// look up bssid and ssid
				c = db.rawQuery("SELECT " + DB_N_ID + " FROM "
						+ DbHelper.tableName + " WHERE " + DB_N_SSID + " = \""
						+ ssid + "\"" + " AND " + DB_N_BSSID + " = \"" + bssid
						+ "\"", null);

				if (c.getColumnCount() > 0 && c.getCount() > 0) {
					c.moveToFirst();
					id = c.getLong(c.getColumnIndexOrThrow(DB_N_ID));
				}
			}

		} catch (SQLException se) {
			Log.d(TAG, "searchForNetwork sql exception");
			se.printStackTrace();
		} finally {
			if (c != null)
				c.close();
			if (db != null)
				db.close();
		}

		return id;
	}

	/**
	 * get all networks with location parameters. Use this method RARELY. The
	 * resulting set may be huge!
	 * 
	 * ATTENTION - not all info 'bout the nets is retrieved from the DB! ! !
	 * 
	 * @param only_open search only for open networks?
	 * @return a set of all networks from the db.
	 */
	public Set<Network> getNetworksWithLocation(boolean only_open) {
		Set<Network> set = null;

		Log.d(TAG, "getNetworksWithLocation(..)");

		SQLiteDatabase db = null;

		Cursor c = null;

		try {
			db = getReadableDatabase();

			c = db.rawQuery("SELECT " + DB_N_ID + ", " + DB_N_BSSID + ", "
					+ DB_N_LOC_LAT + ", " + DB_N_LOC_LON + ", "
					+ DB_N_LOC_SET
					+ (only_open ? ", " + DB_N_OPEN : "")
					+ " FROM "
					+ DbHelper.tableName
					+ (only_open ? " WHERE " + DB_N_OPEN + " = " + 1 : "")
					, null);

			if (c.getCount() > 0) {
				// we have some data

				set = new HashSet<Network>();
				// c.moveToFirst();

				int db_id = c.getColumnIndexOrThrow(DB_N_ID);
				int db_bssid = c.getColumnIndexOrThrow(DB_N_BSSID);
				int db_lat = c.getColumnIndexOrThrow(DB_N_LOC_LAT);
				int db_lon = c.getColumnIndexOrThrow(DB_N_LOC_LON);
				int db_loc = c.getColumnIndexOrThrow(DB_N_LOC_SET);
				int db_open;
				if (only_open)
					db_open = c.getColumnIndexOrThrow(DB_N_OPEN);
				else
					db_open = -1;

				while (c.moveToNext()) {
					Network n = new Network();
					n.id = c.getLong(db_id);
					n.bssid = c.getString(db_bssid);
					n.latitude = c.getDouble(db_lat);
					n.longitude = c.getDouble(db_lon);
					n.locationSet = ((c.getInt(db_loc) == 0) ? false : true);
					if (only_open)
						n.open = ((c.getInt(db_open) == 0) ? false : true);
					set.add(n);
				}
			}
		} catch (SQLException se) {
			Log.d(TAG, "getNetworksWithLocation sql exception");
			se.printStackTrace();
		} finally {
			if (c != null)
				c.close();
			if (db != null)
				db.close();
		}

		return set;
	}

	/**
	 * Gives you a set of networks that have not been sent to the server.
	 * 
	 * @return set of networks or null.
	 */
	public Set<Network> getNetworksNotSend() {
		Set<Network> set = null;

		SQLiteDatabase db = null;

		Cursor c = null;

		try {
			db = getReadableDatabase();

			c = db.rawQuery("SELECT " + "*" + " FROM " + DbHelper.tableName
					+ " WHERE ", new String[] { DB_N_SEND + " = " + 0 });

			if (c.getCount() > 0) {
				// we have some data

				set = new HashSet<Network>();
				c.moveToFirst();

				int db_id = c.getColumnIndexOrThrow(DB_N_ID);
				int db_bssid = c.getColumnIndexOrThrow(DB_N_BSSID);
				int db_lat = c.getColumnIndexOrThrow(DB_N_LOC_LAT);
				int db_lon = c.getColumnIndexOrThrow(DB_N_LOC_LON);
				int db_loc = c.getColumnIndexOrThrow(DB_N_LOC_SET);
				// int db_send = c.getColumnIndexOrThrow(DB_N_SEND);
				int db_freq = c.getColumnIndexOrThrow(DB_N_FREQUENCY);
				int db_lvl = c.getColumnIndexOrThrow(DB_N_LEVEL);
				int db_open = c.getColumnIndexOrThrow(DB_N_OPEN);
				int db_security = c.getColumnIndexOrThrow(DB_N_SECURITY);
				int db_ssid = c.getColumnIndexOrThrow(DB_N_SSID);

				do {
					Network n = new Network();
					n.id = c.getLong(db_id);
					n.bssid = c.getString(db_bssid);
					n.latitude = c.getDouble(db_lat);
					n.longitude = c.getDouble(db_lon);
					n.locationSet = ((c.getInt(db_loc) == 0) ? false : true);
					n.send = false;
					n.frequency = c.getInt(db_freq);
					n.level = c.getInt(db_lvl);
					n.open = ((c.getInt(db_open) == 0) ? false : true);
					n.security = c.getString(db_security);
					n.ssid = c.getString(db_ssid);
					set.add(n);
				} while (!c.moveToNext());
			}
		} catch (SQLException se) {
			Log.d(TAG, "getNetworksWithLocation sql exception");
			se.printStackTrace();
		} finally {
			if (c != null)
				c.close();
			if (db != null)
				db.close();
		}

		return set;
	}

	/**
	 * 
	 * @param net
	 * @param sent true, if network was sent to the server.
	 */
	public void setNetworkAsSend(Network net, boolean sent) {
		SQLiteDatabase db = null;

		try {
			db = getWritableDatabase();

			ContentValues cv = new ContentValues();
			cv.put(DB_N_BSSID, net.bssid);
			cv.put(DB_N_SEND, (sent? 1 : 0));

			Log.d(TAG, "setting network (BSSID: " + net.bssid + ") as send");

			db.update(tableName, cv, DB_N_BSSID + " = " + net.bssid, null);

		} catch (SQLException se) {
			Log.d(TAG, "setNetworkAsSend(..) sql exception");
			se.printStackTrace();
		} finally {
			if (db != null)
				db.close();
		}
	}

}
